/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import src.Entities.Idioma;
import src.Entities.Skill;
import src.jdbc.PostgreSQL;

/**
 *
 * @author Felipe
 */
public class SkillDAO
{

    /**+------------------------------------------------+
     * |  ESTRUCTURA TABLA SKILL                        |
     * |                                                |
     * |   CREATE TABLE skill                           |
     * |(                                               |
     * |   skill_id integer NOT NULL,                   |
     * |   nombre character varying(20) NOT NULL,       |
     * |   CONSTRAINT skill_pk PRIMARY KEY (skill_id)   |
     * |)                                               |
     * +------------------------------------------------+
     */
    public String Skill(int skillId)
    {
        try
        {
            String sql = "DELETE FROM skill WHERE skill_id = ?;";
            PreparedStatement delete = PostgreSQL.getConnection().prepareStatement(sql);
            delete.setInt(1, skillId);
            return String.valueOf(delete.execute());
        }
        catch (Exception e)
        {
            return e.toString();
        }
    }

    public String insertSkill(Skill skill)
    {
        try
        {
            String sql = "INSERT " +
                    "       INTO skill(skill_id, nombre)" +
                    "     VALUES ( ?, ?)";
            PreparedStatement insert = PostgreSQL.getConnection().prepareStatement(sql);
            insert.setInt(1, skill.getSkillID());
            insert.setString(2, skill.getNombre());
            return String.valueOf(insert.execute());
        }
        catch (Exception e)
        {
            return e.toString();
        }
    }

    public ArrayList selectSkill(String sql)
    {
        ArrayList results = new ArrayList();
        try
        {
            Connection con = PostgreSQL.getConnection();
            Statement select = con.createStatement();
            ResultSet query = select.executeQuery(sql);
            while (query.next())
            {
                Skill skill = new Skill(query.getInt("skill_id"),
                        query.getString("nombre"));
                results.add(skill);
            }
        }
        catch (Exception e)
        {
            System.out.println(e);
        }
        return results;
    }

    public String updateIdioma(Skill skill)
    {
        try
        {
            String sql = "UPDATE skill" +
                    "        SET nombre = ?" +
                    "      WHERE skill_id = ?";
            PreparedStatement update = PostgreSQL.getConnection().prepareStatement(sql);
            update.setInt(1, skill.getSkillID());
            update.setString(2, skill.getNombre());
            return String.valueOf(update.execute());
        }
        catch (Exception e)
        {
            return e.toString();
        }
    }

    public static List<Skill> listarSkill() throws SQLException
    {
        Connection con = PostgreSQL.getConnection();
        Statement select = con.createStatement();
        String sql = "select skill_id, nombre from skill";
        ResultSet query = select.executeQuery(sql);
        List<Skill> listaSkill = new ArrayList();

        while (query.next())
        {

            Skill skill = new Skill();
            skill.setSkillID(query.getInt("skill_id"));
            skill.setNombre(query.getString("nombre"));
            listaSkill.add(skill);

        }
        return listaSkill;
    }
}
